package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.houdoudou.bean.BeanParseException;
import com.houdoudou.bean.Order;
import com.houdoudou.common.DateTimeFunction;

public class OrderDao {

	 Connection conn=null;	


	public OrderDao(Connection conn) {
		super();
		this.conn = conn;
	}

	public  long create( Order order ) throws SQLException{	
		long result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "insert  into tb_order (state,productNum ,secret ,volume,boxVolume ,boxNum ,isBoxUsed,"    //7 个参数
	   		+ "boxesIn,machineID,machineAddress ,machineName ,dateAA ,timeFlag ,customerTelnum ,supplierTelnum," //8个参数
            + "serviceTelnum,supplierName ,ownerTelnum ,dateTimePL ,dateTimePA ,dateTimeAE,dateTimeAA  ,"  //7个参数
            + "dateTimeAI,courierAITelnum ,dateTimeTK ,dateTimeTL ,dateTimeRK,courierRKTelnum ,dateTimeRL ," //7个参数
            + "dateTimeAB  ,courierABTelnum ,dateTimeRF,dateTimeRDL ,isRefund ,boxFeeCu,moneyFreight ,moneyProduct," //8个参数
	   		+ "moneyCu,boxFeeSu ,rateSu,rateOw ,moneyCompany ,moneyOwner ,moneySupplier ,isMoneyDealed, isMoneyPaid,note,printTimes ) " //11个参数
	        + "values "
	        + "(   ?,?,?,?,?,?,?,?,"
	        + "?,?,?,?,?,?,?,?,?,?,"
	        + "?,?,?,?,?,?,?,?,?,?,"
	        + "?,?,?,?,?,?,?,?,?,?,"
	        + "?,?,?,?,?,?,?,?,?,?)";
	        	//共48个参数。
	        	pstm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	        	pstm.setInt(1, order.getState());
	        	pstm.setInt(2, order.getProductNum());
	        	pstm.setString(3, order.getSecret());  
	        	pstm.setInt(4, order.getVolume()); 
	        	pstm.setInt(5, order.getBoxVolume());  
	        	pstm.setInt(6, order.getBoxNum());
	        	pstm.setInt(7, order.getIsBoxUsed());
	        	
	        	pstm.setString(8, order.getBoxesIn());
	        	pstm.setInt(9, order.getMachineID());
	        	pstm.setString(10, order.getMachineAddress());  
	        	pstm.setString(11, order.getMachineName()); 
	        	pstm.setString(12, order.getDateAA());  
	        	pstm.setInt(13, order.getTimeFlag());
	        	pstm.setString(14, order.getCustomerTelnum());
	        	pstm.setString(15, order.getSupplierTelnum());
	        	
	        	pstm.setString(16, order.getServiceTelnum());
	        	pstm.setString(17, order.getSupplierName());
	        	pstm.setString(18, order.getOwnerTelnum());  
	        	pstm.setString(19, DateTimeFunction.myDateFormat(new Date())); 
	        	pstm.setString(20, order.getDateTimePA()); 
	        	pstm.setString(21, order.getDateTimeAE()); 
	        	pstm.setString(22, order.getDateTimeAA()); 
	        	
	        	
	        	pstm.setString(23, order.getDateTimeAI()); 
	        	pstm.setString(24, order.getCourierAITelnum()); 
	        	pstm.setString(25, order.getDateTimeTK()); 
	        	pstm.setString(26, order.getDateTimeTL()); 
	        	pstm.setString(27, order.getDateTimeRK()); 
	        	pstm.setString(28, order.getCourierRKTelnum()); 
	        	pstm.setString(29, order.getDateTimeRL()); 
	        	
	        	pstm.setString(30, order.getDateTimeAB()); 
	        	pstm.setString(31, order.getCourierABTelnum()); 
	        	pstm.setString(32, order.getDateTimeRF()); 
	        	pstm.setString(33, order.getDateTimeRDL()); 
	        	pstm.setInt(34, order.getIsRefund());
	        	pstm.setDouble(35, order.getBoxFeeCu());
	        	pstm.setDouble(36, order.getMoneyFreight());
	        	pstm.setDouble(37, order.getMoneyProduct());
	        	
	        	pstm.setDouble(38, order.getMoneyCu());
	        	pstm.setDouble(39, order.getBoxFeeSu());
	        	pstm.setDouble(40, order.getRateSu());
	        	pstm.setDouble(41, order.getRateOw());
	        	pstm.setDouble(42, order.getMoneyCompany());
	        	pstm.setDouble(43, order.getMoneyOwner());
	        	pstm.setDouble(44, order.getMoneySupplier());
	        	pstm.setInt(45, order.getIsMoneyDealed());
	        	pstm.setInt(46, order.getIsMoneyPaid());
	        	pstm.setString(47, order.getNote());
	        	pstm.setInt(48, 0); //order.getPrintTimes()
	        	
				pstm.executeUpdate(); 	
				rs=pstm.getGeneratedKeys();
				rs.next();
				result=rs.getLong(1);
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	private String getDeleteSql(Order order){
		String sql="delete  from tb_order ";

		int count=0;
		if(order.getId()!=-1){
			 sql+="where id="+ order.getId();
		}else{
			try {
				throw new BeanParseException("删除订单，没赋值编号");
			} catch (BeanParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		System.out.print(sql);
		return sql;
	}
	public  void delete(Order order) throws SQLException{	
		
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = getDeleteSql(order);		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		
	}
	
	
	
	public  int update( String sql ) throws SQLException, Exception{	
		  int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 		
  
		   try {
	
		System.out.println(sql);
		stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		stm.executeUpdate(sql);			
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	
	public static String getUpdateSql(Order order) throws Exception{
		  int whereCount=1;		
			int setCount=1;	
			int count=1;
		String sql="update tb_order  ";	
		if(order.getState()!=-1) {			
			            if(setCount==1) {sql= sql+" set state = "+order.getState();  
			            }else{sql= sql+" , state = "+order.getState();}			
			            setCount++;   }
		if(order.getBoxNum()!=-1) {			
			if(setCount==1) {sql= sql+" set boxNum = "+order.getBoxNum();
			           }else{sql= sql+" , boxNum = "+order.getBoxNum();}			
			            setCount++;  }
		if(order.getIsBoxUsed()!=-1) {			
			if(setCount==1) {sql= sql+" set isBoxUsed = "+order.getIsBoxUsed();
			           }else{sql= sql+" , isBoxUsed = "+order.getIsBoxUsed();}			
			            setCount++;  }
    	
    	if(!order.getBoxesIn().isEmpty()) {			
			if(setCount==1) {sql= sql+" set boxesIn = "+order.getBoxesIn();
			           }else{sql= sql+" , boxesIn = "+order.getBoxesIn();}			
			            setCount++;  }
    
    	
    	if(!order.getDateTimePA().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimePA = "+order.getDateTimePA();
			           }else{sql= sql+" , dateTimePA = "+order.getDateTimePA();}			
			            setCount++;  }
     	if(!order.getDateTimeAI().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimeAI = "+order.getDateTimeAI();
			           }else{sql= sql+" , dateTimeAI = "+order.getDateTimeAI();}			
			            setCount++;  }
    	if(!order.getCourierAITelnum().isEmpty()) {			
			if(setCount==1) {sql= sql+" set courierAITelnum = "+order.getCourierAITelnum();
			           }else{sql= sql+" , courierAITelnum = "+order.getCourierAITelnum();}			
			            setCount++;  }

    	if(!order.getDateTimeTK().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimeTK = "+order.getDateTimeTK();
			           }else{sql= sql+" , dateTimeTK = "+order.getDateTimeTK();}			
			            setCount++;  }
    	if(!order.getDateTimeRK().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimeRK = "+order.getDateTimeRK();
			           }else{sql= sql+" , dateTimeRK = "+order.getDateTimeRK();}			
			            setCount++;  }
    	if(!order.getCourierRKTelnum().isEmpty()) {			
			if(setCount==1) {sql= sql+" set courierRKTelnum = "+order.getCourierRKTelnum();
			           }else{sql= sql+" , courierRKTelnum = "+order.getCourierRKTelnum();}			
			            setCount++;  }
    	
    	if(!order.getDateTimeAB().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimeAB = "+order.getDateTimeAB();
			           }else{sql= sql+" , dateTimeAB = "+order.getDateTimeAB();}			
			            setCount++;  }
    	if(!order.getCourierABTelnum().isEmpty()) {			
			if(setCount==1) {sql= sql+" set courierABTelnum = "+order.getCourierABTelnum();
			           }else{sql= sql+" , courierABTelnum = "+order.getCourierABTelnum();}			
			            setCount++;  }
    	if(!order.getDateTimeRF().isEmpty()) {			
			if(setCount==1) {sql= sql+" set dateTimeRF = "+order.getDateTimeRF();
			           }else{sql= sql+" , dateTimeRF = "+order.getDateTimeRF();}			
			            setCount++;  }
    	if(order.getIsRefund()!=-1) {			
            if(setCount==1) {sql= sql+" set isRefund = "+order.getIsRefund();  
                      }else{sql= sql+" , isRefund = "+order.getIsRefund();}			
                       setCount++;   }
 
    	if(order.getMoneyCompany()!=-1) {			
            if(setCount==1) {sql= sql+" set moneyCompany = "+order.getMoneyCompany();  
                      }else{sql= sql+" , moneyCompany = "+order.getMoneyCompany();}			
                       setCount++;   }
    	if(order.getMoneyOwner()!=-1) {			
            if(setCount==1) {sql= sql+" set moneyOwner = "+order.getMoneyOwner();  
                      }else{sql= sql+" , moneyOwner = "+order.getMoneyOwner();}			
                       setCount++;   }
    	if(order.getMoneySupplier()!=-1) {			
            if(setCount==1) {sql= sql+" set moneySupplier = "+order.getMoneySupplier();  
                      }else{sql= sql+" , moneySupplier = "+order.getMoneySupplier();}			
                       setCount++;   }
    	if(order.getIsMoneyDealed()!=-1) {			
            if(setCount==1) {sql= sql+" set isMoneyDealed = "+order.getIsMoneyDealed();  
                      }else{sql= sql+" , isMoneyDealed = "+order.getIsMoneyDealed();}			
                       setCount++;   }
    	if(order.getIsMoneyPaid()!=-1) {			
            if(setCount==1) {sql= sql+" set isMoneyPaid = "+order.getIsMoneyPaid();  
                      }else{sql= sql+" , isMoneyPaid = "+order.getIsMoneyPaid();}			
                       setCount++;   }
    	if(order.getPrintTimes()!=-1) {			
            if(setCount==1) {sql= sql+" set printTimes = "+order.getPrintTimes();  
                      }else{sql= sql+" , printTimes= "+order.getPrintTimes();}			
                       setCount++;   }
    	
    	
		if(order.getId()!=-1) {	
			        if(whereCount==1) {sql= sql+" where id = "+order.getId();  }else{sql= sql+" and  id = "+order.getId(); }  
			          whereCount++; 
	    }else{ throw new Exception("更新订单，没传订单编号");}
		System.out.println(sql);
		return sql;
	}
	 public static  String  getSearchSql(Order order){
			String sql="select * from tb_order";
			int count=1;		
			
			if(order.getId()!=-1) {	
				if(count==1) {sql= sql+" where id = "+order.getId();
				         }else{	sql= sql+" and  id = "+order.getId(); }  
				            count++; return sql; }
	
			if(order.getState()!=-1) {	
				if(count==1) {sql= sql+" where state = "+order.getState();
				          }else{sql= sql+" and  state = "+order.getState(); }  
				            count++; }
			if(order.getIsBoxUsed()!=-1) {	
				if(count==1) {sql= sql+" where isBoxUsed = "+order.getIsBoxUsed();
				          }else{sql= sql+" and  isBoxUsed = "+order.getIsBoxUsed(); }  
				            count++; }
			if(order.getMachineID()!=-1) {	
				if(count==1) {sql= sql+" where machineID = "+order.getMachineID();
				          }else{sql= sql+" and  machineID = "+order.getMachineID(); }  
				            count++; }
			if(!order.getDateAA().isEmpty()) {	
				if(count==1) {sql= sql+" where dateAA = "+order.getDateAA();
				          }else{ sql= sql+" and  dateAA = "+order.getDateAA();}  
				           count++; }
			if(order.getTimeFlag()!=-1) {	
				if(count==1) {sql= sql+" where timeFlag = "+order.getTimeFlag();
				          }else{sql= sql+" and  timeFlag = "+order.getTimeFlag(); }  
				            count++; }
			
			if(!order.getCustomerTelnum().isEmpty()) {	
				if(count==1) {sql= sql+" where customerTelnum = "+order.getCustomerTelnum();
				         }else{ sql= sql+" and  customerTelnum  = "+order.getCustomerTelnum();}  
				            count++; }
			if(!order.getSupplierTelnum().isEmpty()) {	
				if(count==1) {sql= sql+" where supplierTelnum = "+order.getSupplierTelnum();
				         }else{ sql= sql+" and  supplierTelnum  = "+order.getSupplierTelnum();}  
				            count++; }
			if(!order.getOwnerTelnum().isEmpty()) {	
				if(count==1) {sql= sql+" where ownerTelnum = "+order.getOwnerTelnum();
				         }else{ sql= sql+" and  ownerTelnum  = "+order.getOwnerTelnum();}  
				            count++; }	
			if(order.getIsRefund()!=-1) {	
				if(count==1) {sql= sql+" where isRefund = "+order.getIsRefund();
				          }else{sql= sql+" and  isRefund = "+order.getIsRefund(); }  
				            count++; }
			if(order.getIsMoneyDealed()!=-1) {	
				if(count==1) {sql= sql+" where isMoneyDealed = "+order.getIsMoneyDealed();
				          }else{sql= sql+" and isMoneyDealed = "+order.getIsMoneyDealed(); }  
				            count++; }
			if(order.getIsMoneyPaid()!=-1) {	
				if(count==1) {sql= sql+" where isMoneyPaid = "+order.getIsMoneyPaid();
				          }else{sql= sql+" and isMoneyPaid = "+order.getIsMoneyPaid(); }  
				            count++; }
			
		System.out.println(sql);
			return sql;
		}

	public List< Order> search( String sql) throws SQLException{	
		List< Order> orders= new ArrayList<Order>();
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
	
	        try {        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Order order = new Order();
					order.setId(rs.getLong("id"));
					order.setState(rs.getInt("state"));
		        	order.setProductNum(rs.getInt("productNum"));
		        	order.setSecret(rs.getString("secret"));  
		        	order.setVolume(rs.getInt("volume")); 
		        	order.setBoxVolume(rs.getInt("boxVolume"));  
		        	order.setBoxNum(rs.getInt("boxNum"));
		        	order.setIsBoxUsed(rs.getInt("isBoxUsed"));
		        	
		        	order.setBoxesIn(rs.getString("boxesIn"));
		        	order.setMachineID(rs.getInt("machineID"));
		        	order.setMachineAddress(rs.getString("machineAddress"));  
		        	order.setMachineName(rs.getString("machineName")); 
		        	order.setDateAA(rs.getString("dateAA"));  
		        	order.setTimeFlag(rs.getInt("timeFlag"));
		        	order.setCustomerTelnum(rs.getString("customerTelnum"));
		        	order.setSupplierTelnum(rs.getString("supplierTelnum"));
		        	
		        	order.setServiceTelnum(rs.getString("serviceTelnum"));
		        	order.setSupplierName(rs.getString("supplierName"));
		        	order.setOwnerTelnum(rs.getString("ownerTelnum"));  
		        	order.setDateTimePL(rs.getString("dateTimePL")); 
		        	order.setDateTimePA(rs.getString("dateTimePA")); 
		        	order.setDateTimeAE(rs.getString("dateTimeAE")); 
		        	order.setDateTimeAA(rs.getString("dateTimeAA")); 
		        	
		        	
		        	order.setDateTimeAI(rs.getString("dateTimeAI")); 
		        	order.setCourierAITelnum(rs.getString("courierAITelnum")); 
		        	order.setDateTimeTK(rs.getString("dateTimeTK")); 
		        	order.setDateTimeTL(rs.getString("dateTimeTL")); 
		        	order.setDateTimeRK(rs.getString("dateTimeRK")); 
		        	order.setCourierRKTelnum(rs.getString("courierRKTelnum")); 
		        	order.setDateTimeRL(rs.getString("dateTimeRL")); 
		        	
		        	order.setDateTimeAB(rs.getString("dateTimeAB")); 
		        	order.setCourierABTelnum(rs.getString("courierABTelnum")); 
		        	order.setDateTimeRF(rs.getString("dateTimeRF")); 
		        	order.setDateTimeRDL(rs.getString("dateTimeRDL")); 
		        	order.setIsRefund(rs.getInt("isRefund"));
		        	order.setBoxFeeCu(rs.getDouble("boxFeeCu"));
		        	order.setMoneyFreight(rs.getDouble("moneyFreight"));
		        	order.setMoneyProduct(rs.getDouble("moneyProduct"));
		        	
		        	order.setMoneyCu(rs.getDouble("moneyCu"));
		        	order.setBoxFeeSu(rs.getDouble("boxFeeSu"));
		        	order.setRateSu(rs.getDouble("rateSu"));
		        	order.setRateOw(rs.getDouble("rateOw"));
		        	order.setMoneyCompany(rs.getDouble("moneyCompany"));
		        	order.setMoneyOwner(rs.getDouble("moneyOwner"));
		        	order.setMoneySupplier(rs.getDouble("moneySupplier"));
		        	order.setIsMoneyDealed(rs.getInt("isMoneyDealed"));
		        	order.setIsMoneyPaid(rs.getInt("isMoneyPaid"));
					order.setNote(rs.getString("note"));
		        	order.setPrintTimes(rs.getInt("printTimes"));
					
					orders.add(order);
				}
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return orders;
	}	
	
	 public Order selectById(long id) throws Exception{
		   String sql="select * from tb_order  where id = "+ id;
		   List<Order> orders= search(sql);
		   Order orderExist = null;
		   if(orders.size()>0){ orderExist= orders.get(0); 
		   }
		   return orderExist;
	   }
}
